package com.marketing.tenant.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.marketing.tenant.entity.TenantSubscription;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 租户订阅Mapper接口
 * 
 * @author Marketing System
 * @since 2024-09-25
 */
@Mapper
public interface TenantSubscriptionMapper extends BaseMapper<TenantSubscription> {
    
    /**
     * 查询租户的当前有效订阅
     * 
     * @param tenantId 租户ID
     * @return 当前订阅
     */
    @Select("SELECT * FROM tenant_subscription WHERE tenant_id = #{tenantId} AND status = 'ACTIVE' AND end_date > NOW() ORDER BY end_date DESC LIMIT 1")
    TenantSubscription selectCurrentSubscription(@Param("tenantId") Long tenantId);
    
    /**
     * 查询租户的订阅历史
     * 
     * @param page 分页参数
     * @param tenantId 租户ID
     * @return 订阅历史分页结果
     */
    @Select("SELECT * FROM tenant_subscription WHERE tenant_id = #{tenantId} ORDER BY created_time DESC")
    IPage<TenantSubscription> selectSubscriptionHistory(Page<TenantSubscription> page, @Param("tenantId") Long tenantId);
    
    /**
     * 查询即将到期的订阅（提前7天提醒）
     * 
     * @return 即将到期的订阅列表
     */
    @Select("SELECT * FROM tenant_subscription WHERE status = 'ACTIVE' AND end_date BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY)")
    List<TenantSubscription> selectExpiringSubscriptions();
    
    /**
     * 查询已过期的订阅
     * 
     * @return 已过期的订阅列表
     */
    @Select("SELECT * FROM tenant_subscription WHERE status = 'ACTIVE' AND end_date < NOW()")
    List<TenantSubscription> selectExpiredSubscriptions();
    
    /**
     * 批量更新过期订阅状态
     * 
     * @return 更新数量
     */
    @Update("UPDATE tenant_subscription SET status = 'EXPIRED', updated_time = NOW() WHERE status = 'ACTIVE' AND end_date < NOW()")
    int updateExpiredSubscriptions();
    
    /**
     * 根据订阅计划查询订阅数量
     * 
     * @param planId 计划ID
     * @return 订阅数量
     */
    @Select("SELECT COUNT(*) FROM tenant_subscription WHERE plan_id = #{planId} AND status IN ('ACTIVE', 'PENDING')")
    int countByPlanId(@Param("planId") Long planId);
    
    /**
     * 查询租户在指定时间段内的订阅记录
     * 
     * @param tenantId 租户ID
     * @param startDate 开始时间
     * @param endDate 结束时间
     * @return 订阅记录列表
     */
    @Select("SELECT * FROM tenant_subscription WHERE tenant_id = #{tenantId} AND created_time BETWEEN #{startDate} AND #{endDate} ORDER BY created_time DESC")
    List<TenantSubscription> selectByDateRange(@Param("tenantId") Long tenantId, 
                                               @Param("startDate") LocalDateTime startDate, 
                                               @Param("endDate") LocalDateTime endDate);
}